﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using DevExpress.XtraGrid.Views.Base;
using LKU8.shoukuan;
using System.Reflection;
 

namespace fuzhu
{
    public partial class FormCol : Form
    {
      string cFilter="()";
      string cTable, cTables;  //主子表联合查询时的定义，根据数据库表进行定义
        string cTableName;  //根据datatble传入的表名，单表设置 
        DataTable dt= new DataTable();
      DataTable dtMo; 
      string constr = "";

        public FormCol()
        {
            InitializeComponent();
        }
        //查询数据时，后台数据表名称传入
        public FormCol(string ctable,string ctables)
        {
            InitializeComponent();
            cTable = ctable;
            cTables = ctables;

            //只有管理员才可以更改
            if (CanShu.isAdmin)
            {

                btnAddRow.Enabled = true;
                btnDelRow.Enabled = true;
                btnGenCol.Enabled = true;
            }
          
        }
        //单据录入时，datatable表传入
        public FormCol(DataTable dDt, string tableName,string conStr,bool bAdmin)
        {
            InitializeComponent();
            cTableName = tableName;
            dt = dDt;
            constr = conStr;

            //只有管理员才可以更改
            if (bAdmin)
            {

                btnAddRow.Enabled = true;
                btnDelRow.Enabled = true;
                btnGenCol.Enabled = true;
            }

        }
        
        #region 加载
        private void FormProductRd_Load(object sender, EventArgs e)
        {
            InitData();
            cFilter = "('";
            if (!string.IsNullOrEmpty(cTable))
            {
                cFilter += cTable + "'";
            }
            if (!string.IsNullOrEmpty(cTables))
            {
                cFilter += ",'" + cTables + "'";
            }
            cFilter += ")";
            if (!string.IsNullOrEmpty(cTableName))
            {
                cFilter = "('" + cTableName + "')";
            }
           
            Cx();
        }
        #endregion

        #region 初始化数据
        private void InitData()
        {
           
        }
        #endregion

    
        #region 查询
        private void btnAdd_Click(object sender, EventArgs e)
        {
         
            Cx();
        
        }
        public void Cx()
        {
            try
            {



                string sql = string.Format(@"SELECT [id]
      ,[ctablename]
      ,[ccolname]
      ,[iwidth]
      ,[bvisible] 
      ,[ccolcaption],ctype,bmust,breadonly,bexcelin,ivisibleindex,iqushusort
  FROM  [zdy_gly_columnset] where ctablename in") + cFilter;
                dtMo = DbHelper2.ExecuteTable(sql, constr);
                dtMo.Columns.Add("chk", typeof(Boolean));
                gridControl1.DataSource = dtMo;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }
        }
      
        #endregion


        #region 增加行
        private void btnAddRow_Click(object sender, EventArgs e)
        {
            try
            {

                DataRow dr = dtMo.NewRow();
                //dr["ddate"] = DateTime.Now.ToString("yyyy-MM-dd");
                //dr["cstatus"] = "未审核";
                //dr["chk"] = true;
                //dr["cmaker"] = GlobalParameters.objUserID;

                dtMo.Rows.Add(dr);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }
        }
        #endregion

        #region 删除
        private void btnDelRow_Click(object sender, EventArgs e)
        {

            gridView1.PostEditor();
            gridView1.UpdateCurrentRow();
            //已录入时间的，怎么处理
            int j = 0;
            DialogResult result = CommonHelper.MsgQuestion("确认要删除已勾选行吗？");
            if (result == DialogResult.Yes)
            {
                try
                {
                    for (int i = dtMo.Rows.Count - 1; i >= 0; i--)
                    {
                        if (Convert.ToBoolean(dtMo.Rows[i]["chk"] == DBNull.Value ? false : dtMo.Rows[i]["chk"]))
                        {
                            //没id，自动保存。有id，判断是否modifyed，如果更改了，update
                            string id = DbHelper.GetDbString(dtMo.Rows[i]["id"]);

                            //没保存的直接删除，保存的，删除数据库
                            if (id == "" || string.IsNullOrEmpty(id))
                            {
                                dtMo.Rows.RemoveAt(i);
                                j++;
                            }
                            else
                            {

                                //dtMo.Rows.RemoveAt(i);
                                string sql = " delete from zdy_gly_columnset where id=@Id ";
                                DbHelper2.ExecuteNonQuery(sql, new SqlParameter[] { new SqlParameter("@Id", id) }, constr);
                                j++;

                                dtMo.Rows.RemoveAt(i);


                            }


                        }
                    }
                }
                catch (Exception ex)
                {
                    CommonHelper.MsgError("删除失败！原因：" + ex.Message);
                }
                if (j > 0)
                {
                    CommonHelper.MsgInformation("删除完成！");

                }
            }

        }
        #endregion

        #region 保存
        private void toolStripButton3_Click(object sender, EventArgs e)
        {

            gridView1.PostEditor();
            gridView1.UpdateCurrentRow();


            try
            {
                SqlConnection conn = new SqlConnection(constr);
                SqlCommand comm = new SqlCommand();
                SqlDataAdapter da = new SqlDataAdapter();
                SqlCommandBuilder cb = new SqlCommandBuilder();
                string sql = @"SELECT [id]
      ,[ctablename]
      ,[ccolname]
      ,[iwidth]
      ,[bvisible] 
      ,[ccolcaption],bmust,breadonly,bexcelin,ivisibleindex,iqushusort
  FROM  [zdy_gly_columnset]";
                comm = conn.CreateCommand();
                comm.CommandText = sql;
                da = new SqlDataAdapter(comm);
                cb = new SqlCommandBuilder(da);
                da.Update(dtMo);

                MessageBox.Show("保存成功！");
                Cx();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }


        }
        #endregion

        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(cTable))
            {
                GenCol(cTable);
            }
            if (!string.IsNullOrEmpty(cTables))
            {
                GenCol(cTables);
            }
            if (!string.IsNullOrEmpty(cTableName))
            {
                GenCol2();
            }
            ////字段集的
            //if (fields.Length>0)
            //{
            //    GenCol3();
            //}
            Cx();
            MessageBox.Show("完成！");
        }

        private void GenCol(string cTbName)
        {
            //string sql=string.Format("delete from zdy_gly_columnset where ctablename='{0}' ",cTbName);
            // DbHelper.ExecuteNonQuery(sql);
//INSERT INTO zdy_gly_columnset(ctablename,ccolname,ccolcaption)
         string   sql = string.Format(@"
SELECT 
    TableName=O.name,  
    ColumnName=C.name,   
    ColumnDesc=CONVERT(VARCHAR(100), ISNULL(PFD.[value],N''))
FROM sys.columns C
    INNER JOIN sys.objects O
        ON C.[object_id]=O.[object_id]
            AND O.type='U'
            AND O.is_ms_shipped=0
    LEFT JOIN sys.extended_properties PFD
        ON PFD.class=1 
            AND C.[object_id]=PFD.major_id 
            AND C.column_id=PFD.minor_id
 WHERE O.name  =  N'{0}'      
ORDER BY O.name,C.column_id ", cTbName);
         DataTable dt = DbHelper2.Execute(sql, constr).Tables[0];
           for(int i=0;i<dt.Rows.Count;i++)
            {
                sql = string.Format(@"
if not exists(select 1 from zdy_gly_columnset where ctablename='{0}' and ccolname='{1}')
INSERT INTO zdy_gly_columnset(ctablename,ccolname,ccolcaption,ivisibleindex,iqushusort)
values ('{0}','{1}','{2}',{3},0) ",
            DbHelper.ToDbValue(dt.Rows[i]["TableName"]),
             DbHelper.ToDbValue(dt.Rows[i]["ColumnName"]),
              DbHelper.ToDbValue(dt.Rows[i]["ColumnDesc"]),
              i*10);
                DbHelper2.ExecuteNonQuery(sql, constr);
            }


            sql = string.Format(@"UPDATE b SET ccolcaption=a.ccolcaption FROM  zdy_gly_columnset a,zdy_gly_columnset b
WHERE (a.ctablename='通用设置' )  AND b.ctablename='{0}'  
AND a.ccolname=b.ccolname AND ISNULL(b.ccolcaption,'')='' ", cTbName);
            DbHelper2.ExecuteNonQuery(sql, constr);
        }

        /// <summary>
        /// 插入字段
        /// </summary>
        private void GenCol2()
        {
            //string sql=string.Format("delete from zdy_gly_columnset where ctablename='{0}' ",cTbName);
            // DbHelper.ExecuteNonQuery(sql);
            //INSERT INTO zdy_gly_columnset(ctablename,ccolname,ccolcaption)
            for(int i = 0;i<dt.Columns.Count;i++)
            {
               string  sql = string.Format(@"
if not exists(select 1 from zdy_gly_columnset where ctablename='{0}' and ccolname='{1}')
INSERT INTO zdy_gly_columnset(ctablename,ccolname,ctype)
values ('{0}','{1}' ,'{2}') ",
            cTableName,
             DbHelper.ToDbValue(dt.Columns[i].ColumnName),
             dt.Columns[i].DataType);
               DbHelper2.ExecuteNonQuery(sql, constr);
            }




         string    sql2 = string.Format(@"UPDATE b SET ccolcaption=a.ccolcaption FROM  zdy_gly_columnset a,zdy_gly_columnset b
WHERE (a.ctablename='通用设置' )  AND b.ctablename='{0}'  
AND a.ccolname=b.ccolname AND ISNULL(b.ccolcaption,'')='' ", cTableName);
         DbHelper2.ExecuteNonQuery(sql2, constr);
        }


 









         









    }

   
}
